package com.example.dao.impl;

import com.example.dao.BranchesDao;
import com.example.entity.Branches;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class BranchesDaoImpl implements BranchesDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public List<Branches> getAll() {
        String sql = "SELECT \n" +
                "\tbranches.id,\n" +
                "\tbranches.`name`,\n" +
                "\tcityAreaId,\n" +
                "\tcityarea.name cityAreaName,\n" +
                "\t`address`,\n" +
                "\t`telephone` \n" +
                "FROM branches\n" +
                "INNER JOIN cityarea\n" +
                "ON branches.`cityAreaId`=cityarea.`id`";
        List<Branches> branches = this.jdbcTemplate.query(sql, (rs, rowNum) -> Branches.builder()
                .id(rs.getInt("id"))
                .name(rs.getString("name"))
                .cityAreaId(rs.getInt("cityAreaId"))
                .cityAreaName(rs.getString("cityAreaName"))
                .address(rs.getString("address"))
                .telephone(rs.getString("telephone"))
                .build());
        return branches;
    }

    @Override
    public Branches getOne(int id) {
        String sql = "SELECT id,`name`,cityAreaId,`address`,`telephone` \n" +
                "FROM branches\n" +
                "WHERE id=?";
        Branches branches = this.jdbcTemplate.queryForObject(sql, (rs, rowNum) -> Branches.builder()
                .id(rs.getInt("id"))
                .name(rs.getString("name"))
                .cityAreaId(rs.getInt("cityAreaId"))
                .address(rs.getString("address"))
                .telephone(rs.getString("telephone"))
                .build(),id);
        return branches;
    }

    @Override
    public int add(Branches branches) {
        String sql = "INSERT INTO branches(`name`,`cityAreaId`,`address`,`telephone`)\n" +
                "VALUES(?,?,?,?)";
        return this.jdbcTemplate.update(sql,
                branches.getName(),branches.getCityAreaId(),branches.getAddress(),branches.getTelephone());
    }

    @Override
    public int delete(int id) {
        String sql = "delete from branches where id = ?";
        return this.jdbcTemplate.update(sql,id);
    }

    @Override
    public int update(Branches branches) {
        String sql = "UPDATE branches\n" +
                "SET `name`=?,`cityAreaId`=?,`address`=?,`telephone`=?\n" +
                "WHERE id=?";
        return this.jdbcTemplate.update(sql,
                branches.getName(),
                branches.getCityAreaId(),
                branches.getAddress(),
                branches.getTelephone(),
                branches.getId());
    }
}
